﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Region.Utils;
using MySql.Data.MySqlClient;

namespace Region
{
  [DBObject(Table = "protokol", PrimaryKeyPole = "ProtokolID")]
  public class Protokol : DBObject    
  {
      /// <summary> Оплачено </summary>
      [DBColumn(PoleType=EPoleType.Int32)]
      public Int32 Pay { get; set; }
      /// <summary> Отправлен в Сбербанк </summary>
      [DBColumn(PoleType = EPoleType.Int32)]
      public Int32 ProtokolSB { get; set; }
      /// <summary> Идентификатор протокола </summary>
      [DBColumn]
      public Int32 ProtokolID { get; set; }
      /// <summary> ТО отдел которому принадлежит протокол </summary>        
      [DBColumn]
      public Int32 OID { get; set; }
      /// <summary> Распоряжение с которым связан протокол </summary>        
      [DBColumn]
      public Int32 RasporID { get; set; }
      /// <summary> Определение скоторым связан </summary>        
      [DBColumn]
      public Int32 DefID { get; set; }
      /// <summary> Статья </summary>        
      [DBColumn]
      public Int32 ArticleID { get; set; }
      /// <summary> Пришёл от </summary>       
      [DBColumn]
      public Int32 ExternNameID { get; set; }      
      public String ExternName { get { return (String)getPole("ExternName"); } set { setPole("ExternName", value); } }
      /// <summary> Контргент к которому привязан протокол </summary>        
      [DBColumn]
      public Int32 ProtokolKontID { get; set; }
      /// <summary> Район </summary>        
      [DBColumn]
      public Int32 ProtokolRaionID { get; set; }
      /// <summary> Отдел </summary>        
      [DBColumn]
      public Int32 ProtokolOtdelID { get; set; }
      /// <summary> Категория бизнеса </summary>        
      [DBColumn]
      public Int32 ProtokolKontTypeID { get; set; }
      /// <summary> Исполнитель </summary>        
      [DBColumn]
      public Int32 ProtokolIspUserID { get; set; }
      /// <summary> Принятые меры </summary>        
      [DBColumn]
      public Int32 MeasureID { get; set; }
      /// <summary> Принятые меры судом </summary>        
      [DBColumn]
      public Int32 MeasureSudID { get; set; }
      /// <summary> Лицо привлекаемое к ответственности 0 - Неуказано; 1 - Юридическое лицо; 2 - Должностное лицо; 3 - Гражданин; 4 -Индивидуальный предприниматель </summary>                
      [DBColumn]
      public Int32 FromTypeID { get; set; }
      /// <summary> Наименование ОСП </summary>               
      [DBColumn]
      public Int32 OspNameID { get; set; }
      public String OspName { get { return (String)getPole("OspName"); } set { setPole("OspName", value); } }
      /// <summary> Вид деятельности </summary>        
      [DBColumn]
      public Int32 ProtokolOkvedID { get; set; }
      /// <summary> Номер протокола </summary>        
      [DBColumn]
      public Int32 ProtokolNum { get; set; }
      /// <summary> Наложеный штраф </summary>        
      [DBColumn]
      public Int32 SumP { get; set; }
      /// <summary> Взысканый штраф </summary>
      [DBColumn]
      public Int32 SumP2 { get; set; }
      /// <summary> Тип проверки 0 - Не указан; 1 - Внеплановая; 2 - Плановая; 3 - Административное раследование </summary>
      [DBColumn]
      public Int32 ProtokolCheckType { get; set; }       
      /// <summary> Строка статистической форма 1-07 </summary>
      [DBColumn]
      public Int32 ProtokolForm107 { get; set; }
      /// <summary> Тип проверки 0 - Не указан; 1 - Распояжение; 2 - Определение; 3 - Внешний протокол </summary>            
      [DBColumn]
      public Int32 ProtokolType { get; set; }
      /// <summary> Дата регистрации в журнале </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolReg { get; set; }
      /// <summary> Дата составления </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolCreate { get; set; }
      /// <summary> Дата рассмотрения </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolView { get; set; }
      /// <summary> Дата исполение постановления </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolIsp { get; set; }
      /// <summary> Дата вступления в законную силу  </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolPower { get; set; }
      /// <summary> Возбуждено  </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolVoz { get; set; }
      /// <summary> Выдано представление  </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolOutShow { get; set; }
      /// <summary> Получено представление  </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolInShow { get; set; }
      /// <summary> Исполнен привод </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolPrivod { get; set; }
      /// <summary> Направлено судебным приставам  </summary>
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate ProtokolSP { get; set; }
      /// <summary> Внешний номер протокола </summary>
      [DBColumn]
      public String ProtokolNumExtern { get; set; }
      /// <summary> ФИО </summary>
      [DBColumn]
      public String ProtokolFIO { get; set; }
      /// <summary> Примечание </summary>
      [DBColumn]
      public String ProtokolComment { get; set; }
      /// <summary> Суть правонаршения </summary>
      [DBColumn]
      public String ProtokolInfo1 { get; set; }
      /// <summary> Принятое решение(Коментарий) </summary>
      [DBColumn]
      public String ProtokolInfo2 { get; set; }
      /// <summary> Строка в форма 1-12</summary>
      [DBColumn]
      public String ProtokolForm112 { get; set; }
      
      public Protokol(Engine engine, Int32 ProtokolID)
          : base(engine, ProtokolID)
      {
          
      }
      /*
      public override void showEditor()
      {
          var F = new Forms.ProtokolForm(m_engine, ProtokolID);
          F.ShowDialog();
      }
      */
      public override bool isPole(String PoleName)
      {
        if (PoleName=="ExternName") return true;
        if (PoleName == "OspName") return true;
        return base.isPole(PoleName);
      }
      /// <summary>Получить значение поля</summary><param name="PoleName">Имя поля</param><returns>значание поля</returns>
      protected override Object getPole(String PoleName)
      {
        if (PoleName == "ExternName") return m_engine.ExternName[(Int32)base.getPole("ExternNameID")];
        if (PoleName == "OspName") return m_engine.OspName[(Int32)base.getPole("OspName")];
        return base.getPole(PoleName);
      }
      public override EPoleType getPoleType(String PoleName) 
      { 
        if (PoleName == "ExternName") return EPoleType.String;
        if (PoleName == "OspName") return EPoleType.String;
        return base.getPoleType(PoleName);
      }
      /// <summary>
      /// Присвоить значение полю
      /// </summary>
      /// <param name="PoleName">Имя поля</param>
      /// <param name="Value">Значание(см. EPoleType)</param>
      /// <returns></returns>
      protected override bool setPole(String PoleName, Object Value)
      {
        if (PoleName=="ExternName")        
        {
            if ((String)Value == "")
            {
                ExternNameID = 0;
                return true;
            }
            foreach(var i in m_engine.ExternName)
              if (i.Value == (String)Value)
              {
                  ExternNameID = i.Key;
                  return true;
              }
            m_engine.addExternName((String)Value);
            foreach (var i in m_engine.ExternName)
              if (i.Value == (String)Value)
              {
                  ExternNameID = i.Key;
                  return true;
              }
            return false;
        }      
        if (PoleName=="OspName")
        {
            if ((String)Value == "")
            {
                OspNameID = 0;
                return true;
            }
            foreach (var i in m_engine.OspName)
                if (i.Value == (String)Value)
                {
                    OspNameID = i.Key;
                    return true;
                }
            m_engine.addOspName((String)Value);
            foreach (var i in m_engine.OspName)
              if (i.Value == (String)Value)
              {
                  OspNameID = i.Key;
                  return true;
              }
            return false;
        }
        return base.setPole(PoleName,Value);
      }
    /// <summary>Проверить и получить список ошибок в распоряжении</summary><returns>Список ошибок</returns>
    public List<String> check()
    {
      List<String> L = new List<string>();
      if (this.ArticleID==0)
        L.Add("Неуказана статья.");
      if (this.ProtokolReg.Empty)
        L.Add("Не указана дата регистрации");
      if (this.ProtokolNum==0)
        L.Add("Не указан номер протокола");
      if (this.FromTypeID==0)
        L.Add("Не указано привлекаемое лицо");
      if (this.RasporID!=0)
      {
        Raspor raspor = m_engine.getRaspor(this.RasporID);
        if (raspor.Valid)
        {
          if ((this.ProtokolCheckType!=1 && this.ProtokolCheckType!=6) && raspor.CheckPlan==false)
            L.Add("Распоряжение внепрановое, а в протоколе указано другое");
          if (this.ProtokolCheckType!=2 && raspor.CheckPlan==true)
            L.Add("Распоряжение прановое, а в протоколе указано другое");
        }
      }
      if (this.RasporID==0 && (this.ProtokolCheckType==1))
        L.Add("Указан внеплан, но не указано распоряжение");
      if (this.RasporID==0 && (this.ProtokolCheckType==6))
        L.Add("Указано эпид расследование, но не указано распоряжение");
      if (this.RasporID==0 && (this.ProtokolCheckType==2))
        L.Add("Указан план, но не указано распоряжение");
      return L;
    }
  }
  public class ProtokolList
  {
      public struct Line
      {
          public Int32 OID;
          public Int32 ProtokolID;
          public Int32 ProtokolNum;
          public DateTime ProtokolReg;
          public Int32 ProtokolIspUserID;
          public Int32 FromTypeID;
          public String ProtokolFIO;
          public Int32 ArticleID;
      }
      private readonly Engine m_engine;
      private List<Line> m_lines;
      private Dictionary<String, Object> m_wheres;
      public Line getLine(Int32 l) { return this.m_lines[l]; }
      public Int32 NumLines { get { return m_lines.Count; } }
      private Int32 m_FullLines;
      public Int32 FullLines { get { return m_FullLines; } } 
      void _ExportListXMLX_Task(Object Param, TaskManager.Task task) {ExportListXMLX((String)Param,task);}
 	    public TaskManager.Task ExportListXMLX_Task(String FileName){ return m_engine.taskManager.addTask(_ExportListXMLX_Task,"Формирование реестра протоколов", FileName);}
      
      public bool ExportListXMLX(String FileName, TaskManager.Task task = null)
      {
        var Otdels = m_engine.Otdels;
        var Raions = m_engine.Raions;
        var EL = new List<Dictionary<String,Object>>();
        var Article = m_engine.Article;
        var Measure = m_engine.Measure;
        var MeasureSud = m_engine.MeasureSud;
        var FromType = m_engine.FromType;
        var OspName = m_engine.OspName;
        var CheckType = m_engine.CheckType;
        var KontType = m_engine.KontType;
        var Osnov = m_engine.Osnov;
        /*
        var Raions = m_engine.Raions;
        var KT = m_engine.KontType;
        
        */
        setWhere("IgnorePage", true);
        var old_lines = new List<Line>(m_lines);
        if (task!=null) task.TaskName = "Чтение из базы данных";
        Filter();
        /*var YNN_List = new Dictionary<Int32,String>(){{0,"Нет"},{1,"Да"},{-1,"[Не указан]"}};
        var NNar_List = new Dictionary<Int32,String>(){{0,"Выявлены"},{1,"Не выявлены"},{-1,"[Не указано]"}};*/
        if (task!=null)
        {
      	  task.TaskName = "Формирование реестра протоколов";
      	  task.TaskMax = m_lines.Count;
      	  task.TaskValue = 0;
        }
        foreach(Line line in m_lines)
        {
          var ExportLine = new Dictionary<String,Object>();
          var Protokol = m_engine.getProtokol(line.ProtokolID);
          ExportLine["%OID%"] = Otdels[Protokol.OID].OtdelName;
          ExportLine["%ProtokolNum%"] = Protokol.ProtokolNum;
          ExportLine["%ProtokolCreate%"] = Protokol.ProtokolCreate;
          ExportLine["%ProtokolReg%"] = Protokol.ProtokolReg;
          ExportLine["%IspUserFIO%"] = (Protokol.ProtokolIspUserID>0)?m_engine.getUser(Protokol.ProtokolIspUserID).FIO:"[Не указано]";
          ExportLine["%ProtokolOtdel%"] = (Protokol.ProtokolOtdelID!=0)?Otdels[Protokol.ProtokolOtdelID].OtdelName:"[Не указан]";
          ExportLine["%ProtokolRaion%"] = (Protokol.ProtokolRaionID!=0)?Raions[Protokol.ProtokolRaionID]:"[Не указан]";
          ExportLine["%ExternName%"] = Protokol.ExternName;
          ExportLine["%ProtokolFIO%"] = Protokol.ProtokolFIO;
          ExportLine["%ProtokolComment%"] = Protokol.ProtokolComment;
          ExportLine["%Article%"] = Protokol.ArticleID!=0?Article[Protokol.ArticleID]:"[Не указана]";
          ExportLine["%ProtokolInfo1%"] = Protokol.ProtokolInfo1;
		      ExportLine["%ProtokolInfo2%"] = Protokol.ProtokolInfo2;
          ExportLine["%ProtokolNumExtern%"] = Protokol.ProtokolNumExtern;
          ExportLine["%Measure%"] = Measure[Protokol.MeasureID];
          ExportLine["%MeasureSud%"] = MeasureSud[Protokol.MeasureSudID];
          ExportLine["%SumP%"] = Protokol.SumP;
          ExportLine["%SumP2%"] = Protokol.SumP2;
          ExportLine["%ProtokolView%"] = Protokol.ProtokolView;
          ExportLine["%ProtokolIsp%"] = Protokol.ProtokolIsp;
          ExportLine["%ProtokolPower%"] = Protokol.ProtokolPower;
          ExportLine["%FromType%"] = FromType[Protokol.FromTypeID];
          ExportLine["%ProtokolSP%"] = Protokol.ProtokolSP;
          ExportLine["%OspName%"] = OspName[Protokol.OspNameID];
          ExportLine["%ProtokolVoz%"] = Protokol.ProtokolVoz;
          
          ExportLine["%ProtokolVoz%"] = Protokol.ProtokolVoz;
          ExportLine["%ProtokolOutShow%"] = Protokol.ProtokolOutShow;
          ExportLine["%ProtokolInShow%"] = Protokol.ProtokolInShow;
          
          ExportLine["%ProtokolPrivod%"] = Protokol.ProtokolPrivod;
          
          ExportLine["%ProtokolInShow%"] = Protokol.ProtokolInShow;
          ExportLine["%ProtokolCheckType%"] = CheckType[Protokol.ProtokolCheckType];
          ExportLine["%ProtokolKontTypeID%"] = KontType[Protokol.ProtokolKontTypeID];
          ExportLine["%ProtokolForm112%"] = Protokol.ProtokolForm112;
          ExportLine["%ProtokolCheckResult%"] = String.Join("\n",Protokol.check().ToArray());
          
          if (Protokol.RasporID!=0)
          {
          	var Raspor = m_engine.getRaspor(Protokol.RasporID);
            if (Raspor.Valid)
            {
            	ExportLine["%Raspor%"] = "№"+Raspor.RasporNum + " от "+ Raspor.Date.ToShortDateString();
            	ExportLine["%RasporNum%"] = Raspor.RasporNum;
            	ExportLine["%RasporDate%"] = Raspor.Date;
            	ExportLine["%RasporPlan%"] = Raspor.CheckPlan?"План":"Внеплан";
            	ExportLine["%RasporIsp%"] = Raspor.IspUserID==0?"[Не указан]":Raspor.IspUser.FIO;
            	ExportLine["%RasporOtdel%"] = Raspor.OtdelID==0?"[Не указан]":Raspor.Otdel.OtdelName;
            	ExportLine["%RasporOsnov%"] = Raspor.OsnovID==0?"[Не указан]":Osnov[Raspor.OsnovID];
            	ExportLine["%RasporAktDate%"] = Raspor.AktDate;
            }
            else
            {
            	ExportLine["%Raspor%"] = "RaspoID:"+Protokol.RasporID;
            	ExportLine["%RasporNum%"] = "";
            	ExportLine["%RasporDate%"] = "";
            	ExportLine["%RasporPlan%"] = "";
            	ExportLine["%RasporIsp%"] = "";
            	ExportLine["%RasporOtdel%"] = "";          	
            	ExportLine["%RasporOsnov%"] = "";
            	ExportLine["%RasporAktDate%"] = "";
            }
          }
          else
          {
          	ExportLine["%Raspor%"] = "";
          	ExportLine["%RasporNum%"] = "";
          	ExportLine["%RasporDate%"] = "";
          	ExportLine["%RasporPlan%"] = "";
          	ExportLine["%RasporIsp%"] = "";
          	ExportLine["%RasporOtdel%"] = "";          	
          	ExportLine["%RasporOsnov%"] = "";
          	ExportLine["%RasporAktDate%"] = "";
          }
          ExportLine["%Def%"] ="";
          if (Protokol.DefID!=0)
          {
          	//var Def = m_engine.get(Protokol.DefID);
          	ExportLine["%Def%"] = "№ +++ от ++.++.++++";
          }
          
          EL.Add(ExportLine);
          if (task!=null) task.TaskValue += 1;
        }      
        setWhere("IgnorePage", false);
        m_lines=old_lines;
        var Template = m_engine.getRegionFile("ReestrProtokol","xlsx");
        var Poles = new Dictionary<String,Object>();
        var Tables = new Dictionary<String,List<Dictionary<String,Object>>>(){{"%OID%",EL}};
        Export.export("xlsx",Template,FileName,Poles,Tables,task);
        return true;
      }
        //Кеш списка отделов
        Dictionary<Int32, Dictionary<Int32, String>> m_IspUserList = new Dictionary<Int32, Dictionary<Int32, String>>();
        /// <summary>
        /// Возвращает список исполнителей у которых есть хотяб одно распоряжение
        /// </summary>
        /// <param name="ParentOtdelID">Отдел</param>        
        public Dictionary<Int32,String> IspUserList( Int32 ParentOtdelID)
        {
            if (m_IspUserList.ContainsKey(ParentOtdelID)) return m_IspUserList[ParentOtdelID];
            var FIO = new List<String>();
            var ID = new List<Int32>();
             Dictionary<Int32,String> R = new Dictionary<int,string>();
            SQLResult r = m_engine.SQLSelect(@"
SELECT protokol.ProtokolIspUserID, FIO 
FROM protokol 
  LEFT JOIN users ON users.UserID = raspor.IspUserID 
  INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=raspor.OtdelID
WHERE  otdel_otdel.ParentOtdelID=@ParentOtdelID
GROUP BY raspor.IspUserID", new SQLParam() { { "@ParentOtdelID", ParentOtdelID } });
            for (int i = 0; i < r.Count; i++) R[(Int32)r[i]["IspUserID"]] = (String)r[i]["FIO"];
            m_IspUserList[ParentOtdelID] = R;
            return R;
        }
        /*public void IspUserList(out String[] IspUserFIO, out Int32[] IspUserID,Int32 ParentOtdelID)
        {
            List<String> FIO = new List<String>();
            List<Int32> ID = new List<Int32>();
            SQLResult r = m_engine.SQLSelect(@"
SELECT raspor.IspUserID, FIO 
FROM raspor 
  LEFT JOIN users ON users.UserID = raspor.IspUserID 
  INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=raspor.OtdelID
WHERE  otdel_otdel.ParentOtdelID=@ParentOtdelID
GROUP BY raspor.IspUserID", new SQLParam() { { "@ParentOtdelID",ParentOtdelID} });
            for (int i = 0; i < r.Count; i++)
            {
                FIO.Add((String)r[i]["FIO"]);
                ID.Add((Int32)r[i]["IspUserID"]);
            }
            IspUserFIO = FIO.ToArray();
            IspUserID = ID.ToArray();
        }*/
        /// <summary>
        /// Возмращает минимальный и максимальный год выписки распоряжений
        /// </summary>
        /// <param name="MinYear"></param>
        /// <param name="MaxYear"></param>
        public void MinMaxRasporYear(out Int32 MinYear, out Int32 MaxYear)
        {
            SQLResult r = m_engine.SQLSelect("SELECT MIN(RasporYear) AS MinYear, MAX(RasporYear) AS MaxYear FROM raspor WHERE 1");
            if (r.Count == 1)
            {
                MinYear = (Int32)r[0]["MinYear"];
                MaxYear = (Int32)r[0]["MaxYear"];
            }
            else
            {
                MinYear = DateTime.Now.Year;
                MaxYear = DateTime.Now.Year;
            }
        }

        public ProtokolList(Engine engine)
        {
            m_engine = engine;
            m_lines = new List<Line>();
            m_wheres = new Dictionary<String, Object>();
        }
        public bool clearWhere()
        {
            m_wheres = new Dictionary<String, Object>();
            return true;
        }
        public bool setWhere(String Where, Object Value)
        {
            m_wheres[Where] = Value;
            return true;
        }
        public bool Filter()
        {
          var Tables = new List<String>();
          var Poles = new List<String>();
          var Wheres = new List<String>();
          var OrderBy = new List<String>();
          var Params = new SQLParam();
          Poles.Add("protokol.OID");
          Poles.Add("protokol.ProtokolID");
          Poles.Add("protokol.ProtokolNum");
          Poles.Add("protokol.ProtokolReg");
          Poles.Add("protokol.ProtokolIspUserID");
          Poles.Add("protokol.FromTypeID");
          Poles.Add("protokol.ProtokolFIO");
          Poles.Add("protokol.ArticleID");

          Tables.Add("protokol");

          Wheres.Add("1");
          OrderBy.Add("protokol.ProtokolNum DESC");
          Int32 ReportID = 0;
          if (m_wheres.ContainsKey("ReportID") && m_wheres["ReportID"] != null && Int32.TryParse(m_wheres["ReportID"].ToString(), out ReportID) &&
              m_wheres.ContainsKey("Table") && m_wheres["Table"] != null &&
              m_wheres.ContainsKey("Row") && m_wheres["Row"] != null &&
              m_wheres.ContainsKey("Col") && m_wheres["Col"] != null)
          {
              Tables.Add("INNER JOIN reportdatalist ON reportdatalist.ObjectID=protokol.ProtokolID");
              Wheres.Add("`reportdatalist`.`ReportID`=@ReportID");
              Wheres.Add("`reportdatalist`.`Table`=@Table");
              Wheres.Add("`reportdatalist`.`Row`=@Row");
              Wheres.Add("`reportdatalist`.`Column`=@Col");
              Wheres.Add("`reportdatalist`.`ObjectType`='Protokol'");
              Params["@ReportID"] = ReportID;
              Params["@Table"] = m_wheres["Table"];
              Params["@Row"] = m_wheres["Row"];
              Params["@Col"] = m_wheres["Col"];
          }

          Int32 ProtokolID, ArticleID, OID, ParentOtdelID, ProtokolYear, CreateYear, ProtokolNumStart, ProtokolNumEnd, ProtokolIspUserID, LineInPage, Page, RasporID;
          foreach(String Pole in new String[] { "ProtokolReg", "ProtokolCreate", "ProtokolView", "ProtokolIsp", "ProtokolPower","ProtokolOutShow","ProtokolInShow","ProtokolPrivod" })
          {
              if (m_wheres.ContainsKey(Pole + "Start") && m_wheres[Pole + "Start"] is DateTime)
              {                    
                  Wheres.Add("protokol." + Pole + " >= @" + Pole + "Start");
                  Params["@" + Pole + "Start"] = Engine.RegionDateToInt((DateTime)m_wheres[Pole+"Start"]);
              }
              if (m_wheres.ContainsKey(Pole + "End") && m_wheres[Pole + "End"] is DateTime)
              {
                  Wheres.Add("protokol." + Pole + " < @" + Pole + "End");
                  Params["@" + Pole + "End"] = Engine.RegionDateToInt((DateTime)m_wheres[Pole + "End"]);
              }
          }
          if (m_wheres.ContainsKey("ProtokolID") && m_wheres["ProtokolID"] != null && Int32.TryParse(m_wheres["ProtokolID"].ToString(), out ProtokolID))
          {
              Wheres.Add("protokol.ProtokolID=@ProtokolID");
              Params["@ProtokolID"] = ProtokolID;
          }

          if (m_wheres.ContainsKey("ArticleID") && m_wheres["ArticleID"] != null && Int32.TryParse(m_wheres["ArticleID"].ToString(), out ArticleID) && ArticleID!=0 )
          {
              Wheres.Add("protokol.ArticleID=@ArticleID");
              Params["@ArticleID"] = ArticleID;
          }
          if (m_wheres.ContainsKey("Article") && m_wheres["Article"] != null && (String)m_wheres["Article"] != "")
          {
              Tables.Add("INNER JOIN article ON article.ArticleID=protokol.ArticleID");
              Wheres.Add("article.Article LIKE @Article");
              Params["@Article"] = "%"+ (String)m_wheres["Article"]+"%";
          }  
          if (m_wheres.ContainsKey("OID") && m_wheres["OID"] != null && Int32.TryParse(m_wheres["OID"].ToString(), out OID) && OID>0)
          {                
              Wheres.Add("protokol.OID=@OID");
              Params["@OID"] = OID;
          }
          if (m_wheres.ContainsKey("ProtokolYear") && m_wheres["ProtokolYear"] != null && Int32.TryParse(m_wheres["ProtokolYear"].ToString(), out ProtokolYear) && ProtokolYear>0)
          {
              Wheres.Add("protokol.ProtokolReg>=@ProtokolReg1");
              Wheres.Add("protokol.ProtokolReg<@ProtokolReg2");
              Params["@ProtokolReg1"] = Engine.RegionDateToInt(new DateTime(ProtokolYear,1,1));
              Params["@ProtokolReg2"] = Engine.RegionDateToInt(new DateTime(ProtokolYear+1,1,1));
          }
          if (m_wheres.ContainsKey("CreateYear") && m_wheres["CreateYear"] != null && Int32.TryParse(m_wheres["CreateYear"].ToString(), out CreateYear) && CreateYear > 0)
          {
              Wheres.Add("protokol.ProtokolReg>=@CreateYear1");
              Wheres.Add("protokol.ProtokolReg<@CreateYear2");
              Params["@CreateYear1"] = Engine.RegionDateToInt(new DateTime(CreateYear, 1, 1));
              Params["@CreateYear2"] = Engine.RegionDateToInt(new DateTime(CreateYear + 1, 1, 1));
          }
          if (m_wheres.ContainsKey("ParentOtdelID") && m_wheres["ParentOtdelID"] != null && Int32.TryParse(m_wheres["ParentOtdelID"].ToString(), out ParentOtdelID) && ParentOtdelID > 0)
          {
              Tables.Add("INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=protokol.OtdelID");
              Wheres.Add("otdel_otdel.ParentOtdelID=@ParentOtdelID");
              Params["@ParentOtdelID"] = ParentOtdelID;
          }
          if (m_wheres.ContainsKey("ProtokolNumStart") && m_wheres["ProtokolNumStart"] != null && Int32.TryParse(m_wheres["ProtokolNumStart"].ToString(), out ProtokolNumStart) && ProtokolNumStart > 0)
          {
              Wheres.Add("protokol.ProtokolNum>=@ProtokolNumStart");
              Params["@ProtokolNumStart"] = ProtokolNumStart;
          }
          if (m_wheres.ContainsKey("ProtokolNumEnd") && m_wheres["ProtokolNumEnd"] != null && Int32.TryParse(m_wheres["ProtokolNumEnd"].ToString(), out ProtokolNumEnd) && ProtokolNumEnd > 0)
          {
              Wheres.Add("protokol.ProtokolNum<=@ProtokolNumEnd");
              Params["@ProtokolNumEnd"] = ProtokolNumEnd;
          }
          if (m_wheres.ContainsKey("ProtokolIspUserID") && m_wheres["ProtokolIspUserID"] != null && Int32.TryParse(m_wheres["ProtokolIspUserID"].ToString(), out ProtokolIspUserID) && ProtokolIspUserID!=0)
          {
              Wheres.Add("protokol.ProtokolIspUserID=@ProtokolIspUserID");
              Params["@ProtokolIspUserID"] = ProtokolIspUserID;
          }
          if (m_wheres.ContainsKey("RasporID") && m_wheres["RasporID"] != null && Int32.TryParse(m_wheres["RasporID"].ToString(), out RasporID) && RasporID!=0)
          {
              Wheres.Add("protokol.RasporID=@RasporID");
              Params["@RasporID"] = RasporID;
          }
          if (m_wheres.ContainsKey("FindText") && m_wheres["FindText"] != null && m_wheres["FindText"].ToString().Trim()!="")
          {
              Wheres.Add("(MATCH(ProtokolNumExtern, ProtokolFIO, protokol.Info, ProtokolInfo1, ProtokolInfo2, ProtokolInfo3, ProtokolComment) AGAINST (@FindText1 IN BOOLEAN MODE))");
              OrderBy.Insert(0, "MATCH(ProtokolNumExtern, ProtokolFIO, protokol.Info, ProtokolInfo1, ProtokolInfo2, ProtokolInfo3, ProtokolComment) AGAINST (@FindText2) DESC");
              Params["@FindText1"] = m_wheres["FindText"].ToString().Trim();
              Params["@FindText2"] = m_wheres["FindText"].ToString().Trim();
              
          }
          if (m_wheres.ContainsKey("ExternName") && m_wheres["ExternName"] != null && m_wheres["ExternName"].ToString().Trim() != "")
          {
              Tables.Add("LEFT JOIN externname ON externname.ExternNameID=protokol.ExternNameID");
              Wheres.Add("(MATCH(ExternName) AGAINST (@ExternName1 IN BOOLEAN MODE))");
              OrderBy.Insert(0,"MATCH(ExternName) AGAINST (@ExternName2 ) DESC");
              Params["@ExternName1"] = m_wheres["ExternName"].ToString().Trim();
              Params["@ExternName2"] = m_wheres["ExternName"].ToString().Trim();
          }
          String OrderBySQL = "";
          String LimitSQL = "";
          if (!(m_wheres.ContainsKey("IgnorePage") && (Boolean)m_wheres["IgnorePage"]))
            if (m_wheres.ContainsKey("LineInPage") && m_wheres["LineInPage"] != null && Int32.TryParse(m_wheres["LineInPage"].ToString(), out LineInPage) && LineInPage > 1)
          {
              if (!m_wheres.ContainsKey("Page") || m_wheres["Page"] == null || !Int32.TryParse(m_wheres["Page"].ToString(), out Page)) Page = 1;
              Int32 Start = (Page - 1) * LineInPage;
              LimitSQL = "LIMIT " + Start + ", " + LineInPage + " ";
          }

          if (OrderBy.Count > 0) OrderBySQL = "ORDER BY " + String.Join(", ", OrderBy.ToArray());
          String SQL = String.Format("SELECT SQL_CALC_FOUND_ROWS {0} FROM {1} WHERE ({2}) {3} {4}",
              String.Join(", ", Poles.ToArray()),
              String.Join(" ", Tables.ToArray()),
              String.Join(") AND (", Wheres.ToArray()),
              OrderBySQL, LimitSQL);
          SQLResult r = m_engine.SQLSelect(SQL,Params);
          SQLResult r2 = m_engine.SQLSelect("SELECT FOUND_ROWS() AS Count",new SQLParam());                        
          m_FullLines = (Int32)(Int64)r2[0]["Count"];
          m_lines.Clear();
          for (Int32 i = 0; i < r.Count; i++)            
          {
              var l = new Line();
              l.OID = (Int32)r[i]["OID"];
              l.ProtokolID = (Int32)r[i]["ProtokolID"];
              l.ProtokolNum = (Int32)r[i]["ProtokolNum"];
              l.ArticleID = (Int32)r[i]["ArticleID"];
              l.FromTypeID = (Int32)r[i]["FromTypeID"];
              l.ProtokolIspUserID = (Int32)r[i]["ProtokolIspUserID"];
              try { l.ProtokolFIO = Engine.RegionStringToString((String)r[i]["ProtokolFIO"]); } catch { l.ProtokolFIO = ""; }
              l.ProtokolReg = Engine.IntToRegionDate((Int32)r[i]["ProtokolReg"]);
              m_lines.Add(l);
          }
          return true;
        }
    }
  public partial class Engine
  {
      /// <summary> Список открытых распояжений </summary>
      private Dictionary<Int32, Protokol> l_Protokol;
      /// <summary> Получить протокол по его идентификатору</summary><param name="ProtokolID">ИД Распоряжения</param><returns>Объект распоряжения</returns>
      public Protokol getProtokol(Int32 ProtokolID)
      {
          if (l_Protokol == null) l_Protokol = new Dictionary<Int32, Protokol>();
          if (l_Protokol.ContainsKey(ProtokolID)) return (Protokol)l_Protokol[ProtokolID];
          var protokol = new Protokol(this, ProtokolID);
          l_Protokol[ProtokolID] = protokol;
          return protokol;
      }
      public Int32 newProtokol(Int32 CurrentUserID)
      {
          var Param = new SQLParam();
          Param["@IspUserID"] = CurrentUserID;
          Param["@OID"] = getOtdel(getUser(CurrentUserID).OtdelID).OID;          
          MySqlCommand cmd;
          SQLEdit(@"INSERT INTO protokol(ProtokolIspUserID,OID) VALUES (@IspUserID,@OID)", Param, out cmd);
          return (Int32)cmd.LastInsertedId;
      }
  }

}
                